/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.TempUploadFileDao;
import com.feib.stms.dao.jdbc.rowmapper.TempUploadFileRowMapper;
import com.feib.stms.model.Group;
import com.feib.stms.model.TempUploadFile;
import com.feib.stms.model.UploadFile;
import com.feib.stms.model.User;
import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jimmy Liu
 *
 */
@Repository("tempUploadFileDao")
public class TempUploadFileJdbcDao extends BaseJdbcDao implements TempUploadFileDao 
{

	protected OracleSequenceMaxValueIncrementer sequence;

	protected SimpleJdbcInsert tempUploadFileJdbcInsert;

	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "TEMP_UPLOAD_FILE_PK_SEQ");
		this.tempUploadFileJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.tempUploadFileJdbcInsert.withTableName("TEMP_UPLOAD_FILE")
		.usingColumns("TEMP_UPLOAD_FILE_PK", "GROUP_PK", "FILE_FORMAT", "UPLOAD_USER_PK", "UPLOAD_DATE"
				, "FILE_NAME", "FILE_PATH_ON_SERVER", "FILE_STATUS", "DES_KEY", "FILE_MD5"
				, "UPDATED_DATE", "UPDATED_BY", "CREATED_DATE", "CREATED_BY");
	}

	public int insert(TempUploadFile tempUploadFile) {

		MapSqlParameterSource parameters = new MapSqlParameterSource();

		Long tempUploadFilePk = new Long(sequence.nextLongValue());
		parameters.addValue("TEMP_UPLOAD_FILE_PK",  tempUploadFilePk, Types.NUMERIC);
		parameters.addValue("GROUP_PK",  tempUploadFile.getGroup().getGroupPk(), Types.NUMERIC);
		parameters.addValue("FILE_FORMAT",  tempUploadFile.getFileFormat(), Types.VARCHAR);
		parameters.addValue("UPLOAD_USER_PK",  tempUploadFile.getUploadUser().getUserPk(), Types.NUMERIC);
		parameters.addValue("UPLOAD_DATE", tempUploadFile.getUploadDate(), Types.TIMESTAMP);

		parameters.addValue("FILE_NAME",  tempUploadFile.getFileName(), Types.VARCHAR);
		parameters.addValue("FILE_PATH_ON_SERVER",  tempUploadFile.getFilePathOnServer(), Types.VARCHAR);
		parameters.addValue("FILE_STATUS",  tempUploadFile.getFileStatus(), Types.NUMERIC);
		parameters.addValue("DES_KEY",  tempUploadFile.getDesKey(), Types.VARCHAR);
		parameters.addValue("FILE_MD5",  tempUploadFile.getFileMd5(), Types.VARCHAR);

		parameters.addValue("UPDATED_DATE",  tempUploadFile.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  tempUploadFile.getUpdatedBy(), Types.VARCHAR);

		parameters.addValue("CREATED_DATE",  tempUploadFile.getCreatedDate(), Types.TIMESTAMP);
		parameters.addValue("CREATED_BY", tempUploadFile.getCreatedBy(), Types.VARCHAR);

		int updateCounts = tempUploadFileJdbcInsert.execute(parameters);		

		if (updateCounts > 0)
			tempUploadFile.setTempUploadFilePk(tempUploadFilePk);

		return updateCounts;
		
	}
	
	public PaginatedList<TempUploadFile> queryForPaginatedListByTempUploadFile(TempUploadFile tempUploadFile, Date uploaddDateFrom, Date uploadDateTo, List<String> fileFormats, PaginatedList<TempUploadFile> paginatedList)
	{
		SqlQueryMapProvider sqlQueryMapProvider = this.getSqlQueryMapProviderByTempUploadFile(tempUploadFile, uploaddDateFrom, uploadDateTo, fileFormats, paginatedList);
		return this.queryForPaginatedList(sqlQueryMapProvider, new TempUploadFileRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}
	
	private SqlQueryMapProvider getSqlQueryMapProviderByTempUploadFile(TempUploadFile tempUploadFile, Date uploaddDateFrom, Date uploadDateTo, List<String> fileFormats, PaginatedList<TempUploadFile> paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();

		/* 查詢條件 */
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT TUF.*");
		sql.append(", C1.CODE_NAME AS FILE_FORMAT_NAME, C2.CODE_NAME AS FILE_STATUS_NAME"); //顯示中文名稱
		sql.append(" FROM TEMP_UPLOAD_FILE TUF");
		sql.append(" LEFT JOIN CODE_TABLE C1 ON TUF.FILE_FORMAT = C1.CODE_VALUE AND C1.CODE_KIND = 'UPLOAD_FILE_FORMAT'");
		sql.append(" LEFT JOIN CODE_TABLE C2 ON TUF.FILE_STATUS = C2.CODE_VALUE AND C2.CODE_KIND = 'TEMP_UPLOAD_FILE_STATUS'");
		sql.append(" WHERE 1=1");
		
		if (null != tempUploadFile)
		{
			/* 所屬群組(證券公司) */
			Group group = tempUploadFile.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND TUF.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK",  group.getGroupPk() );
			}
			
			
			User uploadUser = tempUploadFile.getUploadUser();
			if (null != uploadUser)
			{
				/* 上傳人員 */
				if (null != uploadUser.getUserPk())
				{
					sql.append(" AND TUF.UPLOAD_USER_PK = :UPLOAD_USER_PK");
					parameters.put("UPLOAD_USER_PK",  uploadUser.getUserPk() );
				}
				
				/* 可查詢的 證券母/分公司*/
				if (StringUtils.isNotEmpty(uploadUser.getDealSecuritiesNos()))
				{
					List<String> dealSecuritiesNos = Arrays.asList(uploadUser.getDealSecuritiesNos().split(","));// 需轉成List
					if (1 == dealSecuritiesNos.size())
					{
						sql.append(" AND TUF.SECURITIES_NO = :SECURITIES_NO");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos.get(0)); 
					}
					else
					{
						sql.append(" AND TUF.SECURITIES_NO IN (:SECURITIES_NO)");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos); 
					}
					
				}
				
			}			
		}
		
		/* 上傳日期 */
		if (null != uploaddDateFrom && null != uploadDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD')");
			parameters.put("UPLOAD_DATE_FROM",  uploaddDateFrom);
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != uploaddDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM",  uploaddDateFrom);
			
		}
		else if (null != uploadDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != tempUploadFile && null != tempUploadFile.getUploadDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') = TO_CHAR(:UPLOAD_DATE, 'YYYYMMDD' )");
			parameters.put("UPLOAD_DATE",  tempUploadFile.getUploadDate());
			
		}
		
		// 上傳格式
		if (CollectionUtils.isNotEmpty(fileFormats))
		{
			if (1 == fileFormats.size())
			{
				sql.append(" AND TUF.FILE_FORMAT = :fileFormat");
				parameters.put("fileFormat",fileFormats.get(0) );
			}
			else
			{
				sql.append(" AND TUF.FILE_FORMAT IN (:fileFormats)");
				parameters.put("fileFormats",fileFormats );
			}
			
		}
		
		
		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("TUF."))
				{
					orderBy = "TUF." + orderBy;
				}
				
				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);
		return sqlQueryMapProvider;
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.TempUploadFileDao#queryValidateFileByFileName(java.lang.String, java.lang.Long)
	 */
	public List<TempUploadFile> queryValidateFileByFileName(String fileName, Long excludePk)
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT TUF.*");
		sql.append(" FROM TEMP_UPLOAD_FILE TUF");
		sql.append(" WHERE TUF.FILE_NAME LIKE :FILE_NAME").append(" ESCAPE '\\'");// 因檔名有 _，所以使用Like比需使用跳脫字元
		sql.append(" AND TUF.FILE_STATUS != 10"); // 排除檔名錯誤
		sql.append(" AND TUF.FILE_STATUS != 20"); // 排除內容錯誤
		sql.append(" AND TUF.FILE_STATUS != 30"); // 排除系統錯誤

		String[] tokens = fileName.split("_");
		StringBuffer f = new StringBuffer();
		for(String t: tokens)
		{
			if (StringUtils.isNotEmpty(t))
			{
				f.append(t);
			}
			f.append("\\_");
		}

		String escapeFileName = f.toString();

		if (!fileName.endsWith("_"))
		{
			escapeFileName = f.substring(0, f.length()-2);
		}

		parameters.put("FILE_NAME", escapeFileName + "%");
		
		if (null != excludePk)
		{
			sql.append(" AND TUF.TEMP_UPLOAD_FILE_PK != :TEMP_UPLOAD_FILE_PK"); // 排除相同PK
			parameters.put("TEMP_UPLOAD_FILE_PK",  excludePk);
		}

		return this.getSimpleJdbcTemplate().query(sql.toString(), new TempUploadFileRowMapper(), parameters);
	}
	
	public TempUploadFile getTempUploadFileByPk(Long tempUploadFilePk)
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT * FROM TEMP_UPLOAD_FILE WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.put("TEMP_UPLOAD_FILE_PK", tempUploadFilePk);

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);
		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new TempUploadFileRowMapper(), parameters));
	}

	public int deleteByPk(Long tempUploadFilePk)
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("DELETE FROM TEMP_UPLOAD_FILE WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.addValue("TEMP_UPLOAD_FILE_PK", tempUploadFilePk, Types.NUMERIC);

		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
	
	public int updateFileImportStatus(TempUploadFile tempUploadFile) 
	{
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		
		sql.append("UPDATE TEMP_UPLOAD_FILE SET FILE_STATUS = :FILE_STATUS");
		sql.append(", DELIVERY_DATE = :DELIVERY_DATE");
		sql.append(", SECURITIES_NO = :SECURITIES_NO");
		sql.append(", BANK_BRACNCH_NO = :BANK_BRACNCH_NO");
		sql.append(", STOCK_TYPE = :STOCK_TYPE");
		
		sql.append(", DEAL_TYPE = :DEAL_TYPE");
		sql.append(", FILE_SN = :FILE_SN");
		sql.append(", TOTAL_ITEMS = :TOTAL_ITEMS");
		sql.append(", TOTAL_AMOUNT = :TOTAL_AMOUNT");
		sql.append(", TOTAL_NET_PAY = :TOTAL_NET_PAY");
		
		sql.append(", TOTAL_NET_INCOME = :TOTAL_NET_INCOME");
		sql.append(", TOTAL_NET_PAY_COUNT = :TOTAL_NET_PAY_COUNT");
		sql.append(", TOTAL_NET_INCOME_COUNT = :TOTAL_NET_INCOME_COUNT");
		sql.append(", CHKSUM = :CHKSUM");
		sql.append(", FILE_PATH_ON_SERVER = :FILE_PATH_ON_SERVER"); // 若檢核成功，則會把檔案搬移到備份區
		sql.append(", UPDATED_DATE = :UPDATED_DATE");
		sql.append(", UPDATED_BY = :UPDATED_BY");
		sql.append(", PRE_DESIGNATED_ACCOUNT = :PRE_DESIGNATED_ACCOUNT"); //約定帳戶
		
		sql.append(" WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		
		parameters.addValue("FILE_STATUS", tempUploadFile.getFileStatus(), Types.NUMERIC);
		parameters.addValue("DELIVERY_DATE", tempUploadFile.getDeliveryDate(), Types.TIMESTAMP);		
		parameters.addValue("SECURITIES_NO", tempUploadFile.getSecuritiesNo(), Types.VARCHAR);
		parameters.addValue("BANK_BRACNCH_NO", tempUploadFile.getBankBracnchNo(), Types.VARCHAR);
		parameters.addValue("STOCK_TYPE", tempUploadFile.getStockType(), Types.VARCHAR);
		
		parameters.addValue("DEAL_TYPE", tempUploadFile.getDealType(), Types.VARCHAR);
		parameters.addValue("FILE_SN", tempUploadFile.getFileSn(), Types.VARCHAR);
		parameters.addValue("TOTAL_ITEMS", tempUploadFile.getTotalItems(), Types.NUMERIC);
		parameters.addValue("TOTAL_AMOUNT", tempUploadFile.getTotalAmount(), Types.DECIMAL);
		parameters.addValue("TOTAL_NET_PAY", tempUploadFile.getTotalNetPay(), Types.DECIMAL);
		
		parameters.addValue("TOTAL_NET_INCOME", tempUploadFile.getTotalNetIncome(), Types.DECIMAL);
		parameters.addValue("TOTAL_NET_PAY_COUNT", tempUploadFile.getTotalNetPayCount(), Types.NUMERIC);
		parameters.addValue("TOTAL_NET_INCOME_COUNT", tempUploadFile.getTotalNetIncomeCount(), Types.NUMERIC);
		parameters.addValue("CHKSUM", tempUploadFile.getChksum(), Types.NUMERIC);
		parameters.addValue("FILE_PATH_ON_SERVER", tempUploadFile.getFilePathOnServer(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE", tempUploadFile.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY", tempUploadFile.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("PRE_DESIGNATED_ACCOUNT", tempUploadFile.getPreDesignatedAccount(), Types.VARCHAR); //約定帳戶
		
		parameters.addValue("TEMP_UPLOAD_FILE_PK", tempUploadFile.getTempUploadFilePk(), Types.NUMERIC);
		
		/*
		logger.debug("getFileStatus: {}",tempUploadFile.getFileStatus());
		logger.debug("getDeliveryDate: {}",tempUploadFile.getDeliveryDate());		
		logger.debug("getSecuritiesNo: {}",tempUploadFile.getSecuritiesNo());
		logger.debug("getBankBracnchNo: {}",tempUploadFile.getBankBracnchNo());
		logger.debug("getStockType: {}",tempUploadFile.getStockType());
		
		logger.debug("getDealType: {}",tempUploadFile.getDealType());
		logger.debug("getFileSn: {}",tempUploadFile.getFileSn());
		logger.debug("getTotalItems: {}",tempUploadFile.getTotalItems());
		logger.debug("getTotalAmount: {}",tempUploadFile.getTotalAmount());
		logger.debug("getTotalNetPay: {}",tempUploadFile.getTotalNetPay());
		
		logger.debug("getTotalNetIncome: {}",tempUploadFile.getTotalNetIncome());
		logger.debug("getTotalNetPayCount: {}",tempUploadFile.getTotalNetPayCount());
		logger.debug("getTotalNetIncomeCount: {}",tempUploadFile.getTotalNetIncomeCount());
		logger.debug("getChksum: {}",tempUploadFile.getChksum());
		logger.debug("getFilePathOnServer: {}",tempUploadFile.getFilePathOnServer());
		logger.debug("getUpdatedDate: {}",tempUploadFile.getUpdatedDate());
		logger.debug("getUpdatedBy: {}",tempUploadFile.getUpdatedBy());
		logger.debug("getPreDesignatedAccount: {}",tempUploadFile.getPreDesignatedAccount()); //約定帳戶	
		logger.debug("getTempUploadFilePk: {}",tempUploadFile.getTempUploadFilePk());
		*/
		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}
	
	
	public PaginatedList<TempUploadFile> queryForPaginatedListByFormatIs2(TempUploadFile tempUploadFile, Date uploaddDateFrom, Date uploadDateTo, PaginatedList<TempUploadFile> paginatedList)
	{
		SqlQueryMapProvider sqlQueryMapProvider = this.getSqlQueryMapProviderByFormatIs2(tempUploadFile, uploaddDateFrom, uploadDateTo, paginatedList);
		return this.queryForPaginatedList(sqlQueryMapProvider, new TempUploadFileRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}
	
	/**
	 * 查詢可上傳的交割款檔案(含以上傳驗資檔)<BR/>
	 * 交割款上傳使用
	 * @param tempUploadFile
	 * @param uploaddDateFrom
	 * @param uploadDateTo
	 * @param paginatedList
	 * @return
	 */
	private SqlQueryMapProvider getSqlQueryMapProviderByFormatIs2(TempUploadFile tempUploadFile, Date uploaddDateFrom, Date uploadDateTo, PaginatedList<TempUploadFile> paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();

		/* 查詢條件 */
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT TUF.*");
		sql.append(" FROM ( ");
		
		// 查詢暫存檔 - 交割款
		sql.append("SELECT TUF.TEMP_UPLOAD_FILE_PK");
		sql.append(", TUF.GROUP_PK");
		sql.append(", TUF.FILE_FORMAT");
		sql.append(", TUF.UPLOAD_USER_PK");
		sql.append(", TUF.UPLOAD_DATE");
		sql.append(", TUF.FILE_NAME");
		sql.append(", TUF.FILE_PATH_ON_SERVER");
		sql.append(", TUF.FILE_STATUS");
		sql.append(", TUF.DELIVERY_DATE");
		sql.append(", TUF.SECURITIES_NO");
		sql.append(", TUF.BANK_BRACNCH_NO");
		sql.append(", TUF.STOCK_TYPE");
		sql.append(", TUF.DEAL_TYPE");
		sql.append(", TUF.FILE_SN");
		sql.append(", TUF.FILE_MD5");
		sql.append(", TUF.SIGNER_CERT");
		sql.append(", TUF.TOTAL_ITEMS");
		sql.append(", TUF.TOTAL_AMOUNT");
		sql.append(", TUF.TOTAL_NET_PAY");
		sql.append(", TUF.TOTAL_NET_INCOME");
		sql.append(", TUF.TOTAL_NET_PAY_COUNT");
		sql.append(", TUF.TOTAL_NET_INCOME_COUNT");
		sql.append(", TUF.DES_KEY");
		sql.append(", TUF.CHKSUM");
		
		sql.append(", C1.CODE_NAME AS FILE_FORMAT_NAME, C2.CODE_NAME AS FILE_STATUS_NAME"); //顯示中文名稱
		sql.append(" FROM TEMP_UPLOAD_FILE TUF");
		sql.append(" LEFT JOIN CODE_TABLE C1 ON TUF.FILE_FORMAT = C1.CODE_VALUE AND C1.CODE_KIND = 'UPLOAD_FILE_FORMAT'");
		sql.append(" LEFT JOIN CODE_TABLE C2 ON TUF.FILE_STATUS = C2.CODE_VALUE AND C2.CODE_KIND = 'TEMP_UPLOAD_FILE_STATUS'");
		sql.append(" WHERE 1=1");
		
		if (null != tempUploadFile)
		{
			/* 所屬群組(證券公司) */
			Group group = tempUploadFile.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND TUF.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK",  group.getGroupPk() );
			}
			
			
			User uploadUser = tempUploadFile.getUploadUser();
			if (null != uploadUser)
			{
				/* 上傳人員 */
				if (null != uploadUser.getUserPk())
				{
					sql.append(" AND TUF.UPLOAD_USER_PK = :UPLOAD_USER_PK");
					parameters.put("UPLOAD_USER_PK",  uploadUser.getUserPk() );
				}
				
				/* 可查詢的 證券母/分公司*/
				if (StringUtils.isNotEmpty(uploadUser.getDealSecuritiesNos()))
				{
					List<String> dealSecuritiesNos = Arrays.asList(uploadUser.getDealSecuritiesNos().split(","));// 需轉成List
					if (1 == dealSecuritiesNos.size())
					{
						sql.append(" AND TUF.SECURITIES_NO = :SECURITIES_NO");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos.get(0)); 
					}
					else
					{
						sql.append(" AND TUF.SECURITIES_NO IN (:SECURITIES_NO)");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos); 
					}
				}
			}			
		}
		
		/* 上傳日期 */
		if (null != uploaddDateFrom && null != uploadDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD')");
			parameters.put("UPLOAD_DATE_FROM",  uploaddDateFrom);
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != uploaddDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM",  uploaddDateFrom);
			
		}
		else if (null != uploadDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != tempUploadFile && null != tempUploadFile.getUploadDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(TUF.UPLOAD_DATE, 'YYYYMMDD') = TO_CHAR(:UPLOAD_DATE, 'YYYYMMDD' )");
			parameters.put("UPLOAD_DATE",  tempUploadFile.getUploadDate());
			
		}
		
		// 上傳格式
		sql.append(" AND TUF.FILE_FORMAT = :fileFormat2");
		parameters.put("fileFormat2", UploadFile.UPLOAD_FILE_DELIVERY_PAYMENT );
		
		// 聯集 正式區的 驗資檔
		sql.append(" UNION ALL ");
		sql.append("SELECT UF.UPLOAD_FILE_PK as TEMP_UPLOAD_FILE_PK");
		sql.append(", UF.GROUP_PK");
		sql.append(", UF.FILE_FORMAT");
		sql.append(", UF.UPLOAD_USER_PK");
		sql.append(", UF.UPLOAD_DATE");
		sql.append(", UF.FILE_NAME");
		sql.append(", UF.FILE_PATH_ON_SERVER");
		sql.append(", UF.FILE_STATUS");
		sql.append(", UF.DELIVERY_DATE");
		sql.append(", UF.SECURITIES_NO");
		sql.append(", UF.BANK_BRACNCH_NO");
		sql.append(", UF.STOCK_TYPE");
		sql.append(", UF.DEAL_TYPE");
		sql.append(", UF.FILE_SN");
		sql.append(", UF.FILE_MD5");
		sql.append(", UF.SIGNER_CERT");
		sql.append(", UF.TOTAL_ITEMS");
		sql.append(", UF.TOTAL_AMOUNT");
		sql.append(", UF.TOTAL_NET_PAY");
		sql.append(", UF.TOTAL_NET_INCOME");
		sql.append(", UF.TOTAL_NET_PAY_COUNT");
		sql.append(", UF.TOTAL_NET_INCOME_COUNT");
		sql.append(", UF.DES_KEY");
		sql.append(", UF.CHKSUM");
		
		sql.append(", C1.CODE_NAME AS FILE_FORMAT_NAME, C2.CODE_NAME AS FILE_STATUS_NAME"); //顯示中文名稱
		sql.append(" FROM UPLOAD_FILE UF");
		sql.append(" LEFT JOIN CODE_TABLE C1 ON UF.FILE_FORMAT = C1.CODE_VALUE AND C1.CODE_KIND = 'UPLOAD_FILE_FORMAT'");
		sql.append(" LEFT JOIN CODE_TABLE C2 ON UF.FILE_STATUS = C2.CODE_VALUE AND C2.CODE_KIND = 'UPLOAD_FILE_STATUS'");
		sql.append(" WHERE 1=1");
		
		// 檔案狀態
		// 0: 已上傳/待驗資/待審查(已轉入，尚未有任何人審查)
		// 1. 第一次驗資中(驗資檔)
		// 3. 第一次驗資完成(驗資檔)
		// 5. 第二次驗資中(驗資檔)
		// 7. 已驗資(驗資檔)
		sql.append(" AND UF.FILE_STATUS IN (0, 1, 3, 5, 7)"); 
		
		if (null != tempUploadFile)
		{
			/* 所屬群組(證券公司) */
			Group group = tempUploadFile.getGroup();
			if (null != group && null != group.getGroupPk())
			{
				sql.append(" AND UF.GROUP_PK = :GROUP_PK");
				parameters.put("GROUP_PK",  group.getGroupPk() );
			}
			
			
			User uploadUser = tempUploadFile.getUploadUser();
			if (null != uploadUser)
			{
				/* 上傳人員 */
				if (null != uploadUser.getUserPk())
				{
					sql.append(" AND UF.UPLOAD_USER_PK = :UPLOAD_USER_PK");
					parameters.put("UPLOAD_USER_PK",  uploadUser.getUserPk() );
				}
				
				/* 可查詢的 證券母/分公司*/
				if (StringUtils.isNotEmpty(uploadUser.getDealSecuritiesNos()))
				{
					List<String> dealSecuritiesNos = Arrays.asList(uploadUser.getDealSecuritiesNos().split(","));// 需轉成List
					if (1 == dealSecuritiesNos.size())
					{
						sql.append(" AND UF.SECURITIES_NO = :SECURITIES_NO");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos.get(0)); 
					}
					else
					{
						sql.append(" AND UF.SECURITIES_NO IN (:SECURITIES_NO)");
						parameters.put("SECURITIES_NO",  dealSecuritiesNos); 
					}
				}
			}			
		}
		
		/* 上傳日期 */
		if (null != uploaddDateFrom && null != uploadDateTo)
		{
			// between 上傳起迄
			sql.append(" AND TO_CHAR(UF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD')");
			sql.append(" AND TO_CHAR(UF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD')");
			parameters.put("UPLOAD_DATE_FROM",  uploaddDateFrom);
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != uploaddDateFrom)
		{
			// >=上傳起
			sql.append(" AND TO_CHAR(UF.UPLOAD_DATE, 'YYYYMMDD') >= TO_CHAR(:UPLOAD_DATE_FROM, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_FROM", uploaddDateFrom);
			
		}
		else if (null != uploadDateTo)
		{
			// <= 上傳迄
			sql.append(" AND TO_CHAR(UF.UPLOAD_DATE, 'YYYYMMDD') <= TO_CHAR(:UPLOAD_DATE_TO, 'YYYYMMDD') ");
			parameters.put("UPLOAD_DATE_TO",  uploadDateTo);
			
		}
		else if (null != tempUploadFile && null != tempUploadFile.getUploadDate())
		{
			// 單一日期
			sql.append(" AND TO_CHAR(UF.UPLOAD_DATE, 'YYYYMMDD') = TO_CHAR(:UPLOAD_DATE, 'YYYYMMDD' )");
			parameters.put("UPLOAD_DATE",tempUploadFile.getUploadDate());
			
		}
		
		// 上傳格式 - 驗資檔
		sql.append(" AND UF.FILE_FORMAT = :fileFormat1");
		parameters.put("fileFormat1", UploadFile.UPLOAD_FILE_DELIVERY_PAYMENT_CHK );
		

		sql.append(" ) TUF ");
		
		
		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("TUF."))
				{
					orderBy = "TUF." + orderBy;
				}
				
				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);
		return sqlQueryMapProvider;
	}
}
